﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using ADA.Entity;
using System.Data;
using System.Data.SqlClient;

namespace ADA.Data
{
    public class DALPost
    {
        //private string conStr = ConfigurationManager.AppSettings["connectionString"];
        private string conStr = Common.CONNECTION_STRING;

        public DataTable getAllPost()
        {
            DataTable tblPostInfo = new DataTable();

            string sql = @"SELECT   [Post].[ID],
                                    [Post].[UserID],
                                    [Rice].[TypeName],
                                    [Post].[Amount],
                                    [Post].[Price],
                                    [Province].[Name],
                                    [Post].[CreateDate],
                                    [Post].[Status]
                            FROM    [Post], [Rice], [Province]
                            WHERE   [Post].[RiceID]=[Rice].[ID]
                                AND [Post].[ProvinceID]=[Province].[ID]";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblPostInfo);
            return tblPostInfo;
        }

        public DataTable getPostInfo(long postID)
        {
            DataTable tblPostInfo = new DataTable();

            string sql = @"SELECT   [Post].[ID],
                                    [Post].[UserID],
                                    [User].[Name] AS [UserName],
                                    [Rice].[TypeName],
                                    [Post].[Amount],
                                    [Post].[Price],
                                    [Post].[Address],
                                    [Province].[Name] AS [Province],
                                    [Post].[CreateDate],
                                    [Post].[Status]
                            FROM    [Post], [Rice], [Province], [User]
                            WHERE   [Post].[RiceID]=[Rice].[ID]
                                AND [Post].[ProvinceID]=[Province].[ID]
                                AND [Post].[UserID]=[User].[ID]
                                AND [Post].[ID] = @postID";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblPostInfo);
            return tblPostInfo;
        }

        public void deletePostByPostID(long postID)
        {
            string sql = @"DELETE FROM  [Post]
                                WHERE   [Post].[ID] = @postID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void deletePostByUserID(long userID)
        {
            string sql = @"DELETE FROM  [Post]
                                WHERE   [Post].[UserID] = @userID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void deletePostByRiceID(int riceID)
        {
            string sql = @"DELETE FROM  [Post]
                                WHERE   [Post].[RiceID] = @riceID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@RiceID", SqlDbType.Int).Value = riceID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
